/******************************************************************************* 
 * Copyright (C) 2012-2015 Microfountain Technology, Inc. All Rights Reserved. 
 * 
 * Unauthorized copying of this file, via any medium is strictly prohibited.   
 * Proprietary and confidential
 * 
 * Last Modified: 2016-05-17 19:33:20
 ******************************************************************************/
package cn.com.xy.sms.sdk.db;

import java.io.BufferedReader;
import java.util.Hashtable;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import cn.com.xy.sms.sdk.constant.Constant;
import cn.com.xy.sms.sdk.log.LogManager;
import cn.com.xy.sms.sdk.util.FileUtils;
import cn.com.xy.sms.sdk.util.StringUtils;
/*SDK-431 songzhirong 20160530 start*/
import org.json.JSONArray;
import org.json.JSONObject;
/*SDK-431 songzhirong 20160530 end*/

/*SDK-616 hewengao 20160912 start*/
import cn.com.xy.sms.sdk.threadpool.SmartSmsThreadPoolManager;
/*SDK-616 hewengao 20160912 end*/
public class ConversationDBManager {	

    public static final String dataBaseName = "conversation.db";
    /*SDK-431 songzhirong 20160530 start*/
    static final int dataBaseVersion = 2;
    /*SDK-431 songzhirong 20160530 end*/
    static MyDbHelper dbHelper = null;
    static int timeOut = 1000;
    static int sleepTime = 100;
    public static Object dblock = new Object();
    static Hashtable<SQLiteDatabase, Integer> listDB = new Hashtable<SQLiteDatabase, Integer>();

    public static final String CREATE_CONVERSATION_TABLE = "create table  if not exists "
            + "tb_conversation" + " (id INTEGER PRIMARY KEY, msgId TEXT,phone TEXT,type INTEGER,value TEXT, " +
            		"updateTime INTEGER DEFAULT '0',extend TEXT)";
    
    public static final String CREATE_KEY_TABLE = "create table  if not exists "
            + "tb_key" + " (id INTEGER PRIMARY KEY,cId TEXT, key TEXT, value TEXT," +
    		"extend TEXT)";
    
    public static final String CREATE_INDEX = "create index if not exists indx_key_value on "
            + "tb_key (key,value)";
    
    public static final String CREATE_BASE_VALUE_TABLE = "create table  if not exists "
            + "tb_base_value" + " (id INTEGER PRIMARY KEY, msgId TEXT,phone TEXT,value TEXT,updateTime INTEGER DEFAULT '0',flag INTEGER DEFAULT '0',extend TEXT)";
   
    /*SDK-431 songzhirong 20160530 start*/
    public static final String CREATE_T_LOG_TABLE = "create table  if not exists "
            + "t_log" + " (log_id TEXT,date_time DATETIME DEFAULT CURRENT_TIMESTAMP,cls_name TEXT,method_name TEXT,log_name TEXT,log_json TEXT)";

    
    public static final String CREATE_T_EXCEPTION_TABLE = "create table  if not exists "
            + "t_log_exception" + " (except_id  TEXT,date_time DATETIME DEFAULT CURRENT_TIMESTAMP,cls_name TEXT," +
            		"method_name TEXT,log_name TEXT,log_exception TEXT)";
    /*SDK-431 songzhirong 20160530 end*/

    
    private static SQLiteDatabase createSQLiteDatabase(Context contexts) {

        synchronized (listDB) {
            if (listDB.size() >= 10) {
                return null;
            }
            if (contexts == null) {
                LogManager.e("xiaoyuan","createSQLiteDatabase: contexts is null");
                return null;
            }
            MyDbHelper helper = getHelper(contexts);
            SQLiteDatabase db = helper.getReadableDatabase();
            Integer cnt = null;
            if (db != null) {
                cnt = listDB.get(db);
                if (cnt == null) {
                    cnt = 1;
                } else {
                    cnt++;
                }
                listDB.put(db, cnt);
                if (!db.isOpen()) {
                    // LogManager.e("duoqu_dbopen", "is not open.");
                    listDB.remove(db);
                    return null;
                }
            }
            return db;
        }
    }

    public static void close(SQLiteDatabase db) {
        try {
            if (db == null) {
                LogManager.d("duoqu_dbclose", "^^^^^^^^ db close db is null");
                return;
            }
            synchronized (listDB) {
                if (!db.isOpen()) {
                    listDB.remove(db);
                    LogManager.d("duoqu_dbclose", "$$$$$ db close is not open");
                } else {
                    Integer cnt = listDB.get(db);

                    // LogManager.d("duoqu_dbclose", "$$$$$ db close : "+cnt);
                    if (cnt == null) {
                        LogManager.d("duoqu_dbclose",
                                "$$$$$ db close cnt is null ");
                        // 这种情况如果发生就是有问题的
                        // db.close();
                    } else {
                        cnt = cnt - 1;
                        if (cnt == 0) {
                            listDB.remove(db);
                            db.close();
                            // LogManager.e("duoqu_dbclose",
                            // "$$$$$ db close : "+cnt);
                        } else {
                            listDB.put(db, cnt);
                        }
                    }
                }
            }
            if (listDB.size() == 0) {
                // LogManager.d("duoqu_dbclose", "%%%%%%%%%% listDB size is 0");
                if (dbHelper != null) {
                    // dbHelper.close();
                }
                // System.gc();
            }
        } catch (Throwable e) {
            LogManager
                    .e("xiaoyuan", "DBManager close error: " + e.getMessage());
        }

    }

    public static SQLiteDatabase getSQLiteDatabase() {

        SQLiteDatabase sb = null;
        long start = System.currentTimeMillis();
        long end = 0;
        while (sb == null) {

            sb = createSQLiteDatabase(Constant.getContext());
            if (sb != null) {
                if (sb.inTransaction()) {
                    LogManager.d(
                            "xiaoyuan_test",
                            "getSQLiteDatabase inTransaction hashcode: ");
                }
                return sb;
            }
            end = System.currentTimeMillis();
            end = (end - start);
            if (end >= timeOut) {
                break;
            }
            try {
                Thread.sleep(sleepTime);

            } catch (InterruptedException e) {
                LogManager.e("xiaoyuan", "DBManager getSQLiteDatabase error: ", e);
            }
        }
        return sb;
    }

    private synchronized static MyDbHelper getHelper(Context contexts) {

        if (dbHelper == null) {
            dbHelper = new MyDbHelper(contexts, dataBaseName, null,
                    dataBaseVersion);
        }
        return dbHelper;
    }

    private static class MyDbHelper extends SQLiteOpenHelper {
        public MyDbHelper(Context contexts, String name, CursorFactory factory,
                int version) {
            super(contexts, name, factory, version);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {

            try {
                createDb(db);
            } catch (Throwable e) {
                // e.printStackTrace();
                LogManager.e("xiaoyuan","MyDbHelper onCreate error: ", e);
            }
        }

        @Override
        public void onOpen(SQLiteDatabase db) {
            try {
                super.onOpen(db);
            } catch (Throwable e) {
                /*SDK-550 hewengao 20160719 start*/
                LogManager.e("xiaoyuan",
                        "MyDbHelper onOpen error: ", e);
                /*SDK-550 hewengao 20160719 end*/
            }
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            createDb(db);
        }

        @Override
        public void onDowngrade(SQLiteDatabase db, int oldVersion,
                int newVersion) {
            try {
                db.execSQL("DROP TABLE IF EXISTS tb_base_value");
                db.execSQL("DROP TABLE IF EXISTS tb_conversation");
                db.execSQL("DROP TABLE IF EXISTS tb_key");
                /*SDK-431 songzhirong 20160530 start*/
                db.execSQL("DROP TABLE IF EXISTS t_log");
                db.execSQL("DROP TABLE IF EXISTS t_log_exception");
                /*SDK-431 songzhirong 20160530 end*/

            
            } catch (Throwable e) {
                /*SDK-550 hewengao 20160719 start*/
                LogManager.e("xiaoyaun","DBManager onDowngrade" + e.getMessage(), e);
                /*SDK-550 hewengao 20160719 end*/
            }
            createDb(db);
        }
    }

    public static void createDb(SQLiteDatabase SQLiteDatabase) {
        try {
            SQLiteDatabase.execSQL(CREATE_BASE_VALUE_TABLE);
            SQLiteDatabase.execSQL(CREATE_CONVERSATION_TABLE);
            SQLiteDatabase.execSQL(CREATE_KEY_TABLE);
            SQLiteDatabase.execSQL(CREATE_INDEX);
            /*SDK-431 songzhirong 20160530 start*/
            SQLiteDatabase.execSQL(CREATE_T_LOG_TABLE);
            SQLiteDatabase.execSQL(CREATE_T_EXCEPTION_TABLE);
            /*SDK-431 songzhirong 20160530 end*/

            


        } catch (Throwable e) {
            // e.printStackTrace();
            /*SDK-550 hewengao 20160719 start*/
        	LogManager.e(Constant.TAG, "createDb error:", e);
        	/*SDK-550 hewengao 20160719 end*/
        }

    }

    private static void addColumn(SQLiteDatabase SQLiteDatabase, String sql) {
        try {
            SQLiteDatabase.execSQL(sql);
        } catch (Throwable e) {
        }
    }

    private static SQLiteDatabase getReadSQLiteDatabase() {
        return getSQLiteDatabase();
    }

    public static XyCursor query(String tableName, String[] columns,
            String selection, String[] selectionArgs) throws Exception {
        return query(false, tableName, columns, selection, selectionArgs, null,
                null, null, null);
    }

    private static boolean dbInTransaction(SQLiteDatabase mySQLiteDatabase) {
        if (mySQLiteDatabase != null && mySQLiteDatabase.inTransaction()) {
            close(mySQLiteDatabase);
            LogManager.d("xiaoyuan", "DBManager db inTransaction threadName:"
                   ,Thread.currentThread().getName());
            return true;
        }
        return false;
    }

    public static XyCursor query(boolean distinct, String tableName,
            String[] columns, String selection, String[] selectionArgs,
            String groupBy, String having, String orderBy, String limit)
            throws Exception {

        XyCursor xyCursor = null;
        SQLiteDatabase mySQLiteDatabase = null;
        try {
            mySQLiteDatabase = getReadSQLiteDatabase();
            if(mySQLiteDatabase == null){
                return null;
            }
            if (dbInTransaction(mySQLiteDatabase)) {
                return null;
            }
            Cursor cursor = mySQLiteDatabase.query(distinct, tableName,
                    columns, selection, selectionArgs, groupBy, having,
                    orderBy, limit);
            /* SDK-531 huangzhiqiang 20160711 start */
            xyCursor = new XyCursor(mySQLiteDatabase, cursor, 3);
            /* SDK-531 huangzhiqiang 20160711 end */
        } catch (Throwable e) {
            close(mySQLiteDatabase);
            LogManager.e("xiaoyuan","DBManager 1query error: ", e);
        }
        return xyCursor;
    }

    public static XyCursor query(String tableName, String[] columns,
            String selection, String[] selectionArgs, String groupBy,
            String having, String orderBy, String limit) throws Exception {
        XyCursor xyCursor = null;
        SQLiteDatabase mySQLiteDatabase = null;
        try {
            mySQLiteDatabase = getSQLiteDatabase();
            if (dbInTransaction(mySQLiteDatabase)) {
                return null;
            }
            Cursor cursor = mySQLiteDatabase.query(tableName, columns,
                    selection, selectionArgs, groupBy, having, orderBy, limit);
            /* SDK-531 huangzhiqiang 20160711 start */
            xyCursor = new XyCursor(mySQLiteDatabase, cursor, 3);
            /* SDK-531 huangzhiqiang 20160711 end */
        } catch (Throwable e) {
            close(mySQLiteDatabase);
            /*SDK-550 hewengao 20160719 start*/
            LogManager.e("xiaoyuan","DBManager 2query error: ", e);
            /*SDK-550 hewengao 20160719 end*/
        } finally {

        }
        return xyCursor;
    }

    public static int delete(String tableName, String whereClause,
            String[] whereArgs) throws Exception {

        synchronized (dblock) {
            int res = -1;
            SQLiteDatabase mySQLiteDatabase = null;
            try {
                mySQLiteDatabase = getSQLiteDatabase();
                res = mySQLiteDatabase
                        .delete(tableName, whereClause, whereArgs);
                // LogManager.i("delete res:"+res);
            } catch (Throwable e) {
                res = -1;
            } finally {
                close(mySQLiteDatabase);
            }
            return res;
        }
    }


    public static int update(String tableName, ContentValues values,
            String whereClause, String[] whereArgs) throws Exception {
        synchronized (dblock) {
            int res = -1;
            SQLiteDatabase mySQLiteDatabase = null;
            try {
                mySQLiteDatabase = getSQLiteDatabase();
                res = mySQLiteDatabase.update(tableName, values, whereClause,
                        whereArgs);
            } catch (Throwable e) {
                res = -1;
            } finally {
                close(mySQLiteDatabase);
            }
            return res;
        }
    }

    public static long insert(String tableName, ContentValues values)
            throws Exception {
        synchronized (dblock) {
            long res = -1;
            SQLiteDatabase mySQLiteDatabase = null;
            try {
                mySQLiteDatabase = getSQLiteDatabase();
                res = mySQLiteDatabase.insert(tableName, null, values);
            } catch (Throwable e) {
                res = -1;
            } finally {
                close(mySQLiteDatabase);
            }
            return res;
        }
    }

    public static XyCursor rawQuery(String sql, String[] selectionArgs)
            throws Exception {
        SQLiteDatabase mySQLiteDatabase = null;
        XyCursor xyCursor = null;

        try {
            mySQLiteDatabase = getSQLiteDatabase();
            if (dbInTransaction(mySQLiteDatabase)) {
                return null;
            }
            Cursor cursor = mySQLiteDatabase.rawQuery(sql, selectionArgs);
            /* SDK-531 huangzhiqiang 20160711 start */
            xyCursor = new XyCursor(mySQLiteDatabase, cursor, 3);
            /* SDK-531 huangzhiqiang 20160711 end */
        } catch (Throwable e) {
            close(mySQLiteDatabase);
            /*SDK-550 hewengao 20160719 start*/
            LogManager.e("xiaoyuan","DBManager rawQuery error: ", e);
            /*SDK-550 hewengao 20160719 end*/
        }
        return xyCursor;
    }

    public static void closeDB(String filePath, boolean isDeleteFile,
            java.io.LineNumberReader r, BufferedReader bw, SQLiteDatabase db) {
        try {
            try {
                if (isDeleteFile) {
                    FileUtils.deleteAllFile(filePath);
                }
            } catch (Throwable e2) {
                // TODO: handle Throwable
            }

            try {
                if (r != null)
                    r.close();
            } catch (Throwable e2) {
                // TODO: handle Throwable
            }

            try {
                if (bw != null)
                    bw.close();
            } catch (Throwable e2) {
                // TODO: handle Throwable
            }

            if (db != null) {
                try {
                    if (db.inTransaction()) {
                        db.setTransactionSuccessful();
                        db.endTransaction();
                    }
                } catch (Throwable e) {
//                    e.printStackTrace();
                    /*SDK-550 hewengao 20160719 start*/
                    LogManager.e(Constant.TAG, "closeDB: ", e);
                    /*SDK-550 hewengao 20160719 end*/
                } finally {
                    ConversationDBManager.close(db);
                }
            }
        } catch (Throwable e) {
            // TODO: handle Throwable
            LogManager.e("xiaoyuan","DBManager closeDB error: ", e);
        }

    }

    public static void closeCursor(Cursor cur) {
        if (cur != null) {
            cur.close();
        }
    }

    public static long saveOrUpdateTableData(String tableName,
            ContentValues values, String whereClause, String whereArgs[]) {
        long flag = 0;
        try {
            flag = ConversationDBManager.update(tableName, values, whereClause, whereArgs);
            if (flag < 1) {
                flag = ConversationDBManager.insert(tableName, values);
            } else {
                flag = -flag;
            }
        } catch (Throwable e) {
            // TODO Auto-generated catch block
            /*SDK-550 hewengao 20160719 start*/
            LogManager.e("xiaoyuan", "DBManager saveOrUpdateTableData error: ", e);
            /*SDK-550 hewengao 20160719 end*/
        }
        return flag;
    }
    
    public static void execSQL(final String sql){
       if(StringUtils.isNull(sql)) return;
       /*SDK-616 hewengao 20160912 start*/
       SmartSmsThreadPoolManager.iccidPool.execute(new Runnable() {
       /*SDK-616 hewengao 20160912 end*/
			public void run() {
				try {		
		    		synchronized (ConversationDBManager.dblock) {
		                SQLiteDatabase db = null;
		                try {
		                    db = ConversationDBManager.getSQLiteDatabase();
		                    db.execSQL(sql);		                    
		                } catch (Throwable e) {
		                    /*SDK-550 hewengao 20160719 start*/
		                    LogManager.e(Constant.TAG, "execSQL: ", e);
		                    /*SDK-550 hewengao 20160719 end*/
		                } finally {
		                    ConversationDBManager.close(db);
		                }
		            }
		    	
				} catch (Throwable e) {
				    /*SDK-550 hewengao 20160719 start*/
		            LogManager.e(Constant.TAG, "execSQL: ", e);
		            /*SDK-550 hewengao 20160719 end*/
				}
				  
				
			}
		});
    }
    
    public static void execSQLSyn(final String sql){
  	   if(StringUtils.isNull(sql)) return;

		try {		
    		synchronized (ConversationDBManager.dblock) {
                SQLiteDatabase db = null;
                try {
                    db = ConversationDBManager.getSQLiteDatabase();
                    db.execSQL(sql);
                } catch (Throwable e) {
                    /*SDK-550 hewengao 20160719 start*/
                    LogManager.e(Constant.TAG, "execSQL: ", e);
                    /*SDK-550 hewengao 20160719 end*/
                } finally {
                    ConversationDBManager.close(db);
                }
            }
    	
		} catch (Throwable e) {
		    /*SDK-550 hewengao 20160719 start*/
            LogManager.e(Constant.TAG, "execSQL: ", e);
            /*SDK-550 hewengao 20160719 end*/
		}
		  
		
	
     }
    /*SDK-431 songzhirong 20160530 start*/
    public static JSONArray rawQueryJSONArray(String sql){
    	XyCursor xyCursor = null;
    	try {
    		xyCursor = ConversationDBManager.rawQuery(sql, null);
    		JSONArray jsonArr = new JSONArray();
    		if(xyCursor!=null){
    			while (xyCursor.moveToNext()) {  
    				String[] names = xyCursor.getColumnNames();
    				JSONObject json =  new JSONObject();
    			   	for(int i=0;i<names.length;i++){    			   		
    			   		String value = xyCursor.getString(i); 
    			   		json.put(names[i], value);
    			   	}
    			   	jsonArr.put(json);
                   
                }
    			return jsonArr;
    		}
		} catch (Throwable e) {
			// TODO: handle exception
		}finally{
			XyCursor.closeCursor(xyCursor, true);
		}
    	return null;
    }
    /*SDK-431 songzhirong 20160530 end*/

}
